

package org.veridiandynamics.server.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.veridiandynamics.server.domain.Roles;

/**
 * This class will be the class that manages connections to mySQL, and 
 * it's the only class that actually gives queries to the mySQL server.
 * It will take standard syntax mySQL queries as String inputs and execute 
 * them and return results when appropriate.  
 *
 */
public class Queryprocessor {
	private static String url="jdbc:mysql://localhost:3306/Office";//need to change this to the actual database
	private static String username="root";
	private static String password="password1";
	
  
	/**
	 * Opens and returns one connection object under the input user and password to the mySQL
	 * server and the default database if there are no errors.
	 * 
	 * @param username
	 * @param password
	 * @return the connection
	 */
	private static Connection OpenConnection(String username, String password)
	{
		Connection con=null;
		try {
		Class.forName("com.mysql.jdbc.Driver");
		con = DriverManager.getConnection(url,username, password);
		} catch (Exception e)
		{
			e.printStackTrace();
		}
		return con;
	}
	/**
	 * Closes a connection to server.
	 * 
	 * pre: there is an open connection to the mySQL server.
	 * post: this connection is closed. 
	 * 
	 * @param con
	 */
	private static void CloseConnection(Connection con)
	{
		try {
			con.close();
		} catch (Exception e)
		{
			e.printStackTrace();
		}
	}

	/**
	 * Called to update queries.
	 * 
	 * pre: none
	 * post: This is executes an Update type query, that is non SELECT queries that don't return
	 * results.
	 * 
	 * @param query
	 * @return
	 */
	public static ResultSet RunUpdate(String query)
	{

		PreparedStatement pstmt;
		Connection con;
		ResultSet keys=null;
		con=OpenConnection(username, password);

		try{
	    pstmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
		pstmt.executeUpdate();
		keys=pstmt.getGeneratedKeys();
		} catch (Exception e)
		{
			e.printStackTrace();
		}
		
		CloseConnection(con);
		return keys;
	}


	/**
	 * pre: none
	 * post: this executes a SELECT type query, and returns the result in a ResultSet. 
	 * 
	 * @param query
	 * @return
	 */
	public static ResultSet RunQuery(String query)
	{
		Statement stmt;
		ResultSet result=null;
		Connection con;
		con=OpenConnection(username, password);

		try{
		stmt=con.createStatement();
		result=stmt.executeQuery(query);
		} catch (Exception e)
		{
			e.printStackTrace();
		}
		CloseConnection(con);
		return result;
	}
	
	/**
	 * Returns a list.
	 * 
	 * @param query Query requested.
	 * @return
	 */
	public static List<Integer> IdQuery(String query)
	{
		Statement stmt;
		ResultSet result=null;
		Connection con;
		con=OpenConnection(username, password);
		List<Integer> myList = new ArrayList<Integer>();
		
		try{
		stmt=con.createStatement();
		result=stmt.executeQuery(query);
		} catch (Exception e)
		{
			e.printStackTrace();
		}

		try{
		while(result.next()){
			myList.add(result.getInt(1));
		}
		
		//idarray2=(int[])idarray.getArray();
		} catch (Exception e)
		{
			e.printStackTrace();
		}
		CloseConnection(con);		
		return myList;
	}
	
	/**
	 * Converts a string representing user name to a long ID used as a key to identify user in system.
	 * 
	 * @param username
	 * @return
	 */
	public static Long UsernametoID(String username)
	{
		ResultSet rs;
		Long uid = null;
		Connection con = OpenConnection(Queryprocessor.username, Queryprocessor.password);
		username.replace('"', '\"');
		String query = "SELECT * " +
				"FROM USER " +
				"WHERE USER.USERNAME='" + username + "';";
		
		
		try {
			Statement statement = con.createStatement();
			rs = statement.executeQuery(query);
			
			if(rs.next()) {
				uid = rs.getLong("uid");
			}
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return uid;
	}
	
	
	/**
	 * Manages queries for logging in by taking in user credentials.
	 * 
	 * @param uid user id value for user attempting to log in
	 * @param password password in string for for user attempting to log in
	 * @param role the role the user would like to log in as
	 * @return whether or not the user is successful in logging in
	 */
	public Boolean loginQuery(Long uid, String password, Roles role) {
		
		ResultSet rs;
		password.replace('"', '\"');
		Connection con = OpenConnection(Queryprocessor.username, Queryprocessor.password);
		
		
		
		String query = "SELECT * " +
				"FROM USER " +
				"INNER JOIN USER_ROLE ON USER.uid = USER_ROLE.uid " +
				"INNER JOIN ROLES ON USER_ROLE.RID = ROLES.RID " +
				"WHERE ROLES.ROLE = " + String.valueOf(role.getRole()) + " " +
				"AND USER.PASSWORD = '" + password + "' " +
				"AND USER.uid = " + String.valueOf(uid) + ";";
		
		try {
			Statement statement = con.createStatement();
			rs = statement.executeQuery(query);
			if (rs.next()) {
				con.close();
				return true;
			}
		con.close();
		} catch(SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public static Map<String, Double> getInvoices(Date date) {
		
		ResultSet rs;
		Connection con = OpenConnection(Queryprocessor.username, Queryprocessor.password);
		Date newDate = date;
		newDate.setDate(31);
		
		Map<String, Double> returnMap = new HashMap<String, Double>();
		Double totalBilled = 0.0d;
		
		String query = "SELECT COUNT(*) AS COUNTPATIENTS" +
				"		FROM APPOINTMENT" +
				"		WHERE APPOINTMENT.STARTDATE <= " + date.toString() + 
				"		AND APPOINTMENT.ENDDATE >= " + newDate.toString() + ";";
		try {
			Statement statement = con.createStatement();
			rs = statement.executeQuery(query);
			if (rs.next()) {
				returnMap.put("numberOfPatients", rs.getDouble("COUNTPATIENTS"));
			} else {
				returnMap.put("numberOfPatients", 0.0d);
				returnMap.put("totalBilled", 0.0d);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		query = "SELECT * FROM APPOINTMENT" +
				"INNER JOIN INVOICE ON APPOINTMENT.Invoice_id = INVOICE.IID" +
				"INNER JOIN Invoice_Serv ON INVOICE.IID = Invoice_Serv.IID" +
				"INNER JOIN SERVICESBILLABLE ON Invoice_Serv.SID = SERVICESBILLABLE.SID" +
				"WHERE APPOINTMENT.STARTDATE <= " + date.toString() + 
				"AND APPOINTMENT.ENDDATE >= " + newDate.toString() + ";";
		try {
			Statement statement = con.createStatement();
			rs = statement.executeQuery(query);
			while(rs.next()) {
				Double service = rs.getDouble("COST");
				totalBilled += service;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		returnMap.put("totalBilled", totalBilled);
		return returnMap;
	}
	
}
